package com.gopay.common.cps.dao.order.impl;

import com.gopay.common.base.dao.impl.BaseReadDAOImpl;
import com.gopay.common.cipher.utils.SensitiveInfoUtils;
import com.gopay.common.constants.cps.RefundFlag;
import com.gopay.common.constants.trans.TxnStaCode;
import com.gopay.common.constants.txncd.IntTxnCd;
import com.gopay.common.constants.wallet.WalletConstants;
import com.gopay.common.cps.dao.order.CpsGeneralMainOrderQueryDAO;
import com.gopay.common.domain.cps.CpsGenMainOrder;
import com.gopay.common.util.DateUtils;
import com.gopay.common.util.FastJsonUtil;
import com.gopay.common.util.StringTools;
import org.apache.commons.lang.StringUtils;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.io.Serializable;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 主订单查询DAO
 * 
 * @CpsGeneralMainOrderQueryDAOImpl.java
 * @author MA HUAN
 * @2013-1-8 下午6:51:20 www.gopay.com.cn Inc.All rights reserved.
 */
@SuppressWarnings("all")
@Repository(value = "cpsGeneralMainOrderQueryDAO")
public class CpsGeneralMainOrderQueryDAOImpl extends
		BaseReadDAOImpl<CpsGenMainOrder, Serializable> implements
		CpsGeneralMainOrderQueryDAO {

	private Logger logger = LoggerFactory.getLogger(CpsGeneralMainOrderQueryDAOImpl.class);


	private static AtomicInteger ai = new AtomicInteger(0);

	private static Object obj = new Object();

	@Override
	public CpsGenMainOrder getSuccessPayOrder(String orderId, String merId,
			String merOrderId) {
		String codes = "('" + TxnStaCode.TXN_STA_CD_20000.value + "','"
				+ TxnStaCode.TXN_STA_CD_30102.value + "','"
				+ TxnStaCode.TXN_STA_CD_90201.value + "')";
		String hql = " select m from CpsGenMainOrder m where m.gopayOrderId != '"
				+ orderId
				+ "' and m.merId = '"
				+ merId
				+ "' and m.merOrderId = '"
				+ merOrderId
				+ "' and m.txnStaCd in " + codes;
		// List<CpsGenMainOrder> ss = getHibernateTemplate().find(hql);
		List<CpsGenMainOrder> ss = findByHql(hql);
		return (null == ss || 0 == ss.size()) ? null : ss.get(0);
	}

	@Override
	public List<CpsGenMainOrder> getSuccessPayOrders(String orderId, String merId,
											  String merOrderId) {
		String codes = "('" + TxnStaCode.TXN_STA_CD_20000.value + "','"
				+ TxnStaCode.TXN_STA_CD_30102.value + "','"
				+ TxnStaCode.TXN_STA_CD_90201.value + "')";
		String hql = " select m from CpsGenMainOrder m where m.gopayOrderId != '"
				+ orderId
				+ "' and m.merId = '"
				+ merId
				+ "' and m.merOrderId = '"
				+ merOrderId
				+ "' and m.txnStaCd in " + codes;
		// List<CpsGenMainOrder> ss = getHibernateTemplate().find(hql);
		List<CpsGenMainOrder> ss = findByHql(hql);
		return (null == ss || 0 == ss.size()) ? null : ss;
	}

	@Override
	public List<CpsGenMainOrder> findCpsGenMainOrderByMerIdAndMerOrder(
			String merId, String merOrder) {

		String hql = " from CpsGenMainOrder where merId = :merId and merOrderId = :merOrderId";

		Map<String, Object> param = new HashMap<String, Object>();
		param.put("merId", merId);
		param.put("merOrderId", merOrder);

		return findByHql(hql, param);
	}

	@Override
	public CpsGenMainOrder findByGopayOrderId(String gopayOrderId) {
		String hql = " select m from CpsGenMainOrder m where m.gopayOrderId = '"
				+ gopayOrderId + "'";
		List<CpsGenMainOrder> ss = findByHql(hql);
		return (null == ss || 0 == ss.size()) ? null : ss.get(0);
	}

	/**获取累计待审核退款金额SQL : GopayIntTxnCd='01200' and txnStaCd='30000' */
	private final String FIND_TOTAL_01_REFUND_AMT_SQL = "       SELECT SUM(M.MER_TXN_AMT)                           "
			+ "\r\n"
			+ "         FROM CPS_GEN_MAIN_ORDER M                         "
			+ "\r\n"
			+ "        WHERE M.RELEVANCE_GOPAY_ORDER_ID =:gopayOrderId    "
			+ "\r\n"
			+ "          AND M.GOPAY_INT_TXN_CD = '01200'                 "
			+ "\r\n"
			+ "          AND M.TXN_STA_CD = '"
			+ TxnStaCode.TXN_STA_CD_30000.value
			+ "'                          "
			+ "\r\n";

	@Override
	public BigDecimal findTotal01RefundAmt(final String gopayOrderId) {
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("gopayOrderId", gopayOrderId);
		List result = findBySql(FIND_TOTAL_01_REFUND_AMT_SQL, param);

		/*
		 * List<?> result = this.getHibernateTemplate().executeFind(new
		 * HibernateCallback() { public Object doInHibernate(Session session)
		 * throws HibernateException, SQLException { Query query =
		 * session.createSQLQuery(FIND_TOTAL_01_REFUND_AMT_SQL);
		 * query.setParameter("gopayOrderId", gopayOrderId); return
		 * query.list(); } });
		 */
		if (result != null && result.size() > 0) {
			BigDecimal re = new BigDecimal(result.get(0) == null ? "0"
					: String.valueOf(result.get(0))).setScale(2);
			return re;
		}
		return new BigDecimal("0").setScale(2);
	}

	/** 获取累计待审核退还手续费金额SQL */
	private final String FIND_TOTAL_01_REFUND_FEE_AMT_SQL = "      SELECT SUM(A.REFUND_FEE_AMT)                                  "
			+ "\r\n"
			+ "        FROM CPS_GEN_ASSIST_ORDER A                                 "
			+ "\r\n"
			+ "       WHERE EXISTS (SELECT 1                                       "
			+ "\r\n"
			+ "                FROM CPS_GEN_MAIN_ORDER M                           "
			+ "\r\n"
			+ "               WHERE M.RELEVANCE_GOPAY_ORDER_ID = :gopayOrderId     "
			+ "\r\n"
			+ "                 AND M.GOPAY_INT_TXN_CD = '01200'                   "
			+ "\r\n"
			+ "                 AND M.TXN_STA_CD = '"
			+ TxnStaCode.TXN_STA_CD_30000.value
			+ "'                            "
			+ "\r\n"
			+ "                 AND M.GOPAY_ORDER_ID = A.GOPAY_ORDER_ID)           "
			+ "\r\n";

	@Override
	public BigDecimal findTotal01RefundFeeAmt(final String gopayOrderId) {

		Map<String, Object> param = new HashMap<String, Object>();
		param.put("gopayOrderId", gopayOrderId);
		List result = findBySql(FIND_TOTAL_01_REFUND_FEE_AMT_SQL, param);

		/*
		 * List<?> result = this.getHibernateTemplate().executeFind(new
		 * HibernateCallback<Object>() { public Object doInHibernate(Session
		 * session) throws HibernateException, SQLException { Query query =
		 * session.createSQLQuery(FIND_TOTAL_01_REFUND_FEE_AMT_SQL);
		 * query.setParameter("gopayOrderId", gopayOrderId); return
		 * query.list(); } });
		 */
		if (result != null && result.size() > 0) {
			BigDecimal re = new BigDecimal(result.get(0) == null ? "0"
					: String.valueOf(result.get(0))).setScale(2);
			return re;
		}
		return new BigDecimal("0").setScale(2);
	}

	// 获取除银行订单以外的超时订单
	private final String FIND_TIMEOUT_ORDER_BY_TIMESPAN_SQL = "SELECT  c.gopay_order_id  FROM CPS_GEN_MAIN_ORDER C "
			+ "  where(C.GOPAY_INT_TXN_CD in ('00100','00200','00300','00400','00500')  "
			+ "  AND C.PAY_CHANNEL in ('01','02','03','04','05','06') "
			+ "  AND C.TXN_STA_CD IN ('30000','30101')"
			+ " AND C.GATEWAY_SOURCE != 05 "
			+ " AND ROUND(TO_NUMBER(SYSDATE - (C.CREATED_TS+0))*24*60) >= :timeSpan) "
			+ " OR ( C.GOPAY_INT_TXN_CD ='00500' AND C.PAY_CHANNEL in ('49','98') "
			//+ " AND C.GATEWAY_SOURCE != 05 "对于企业版钱包的充值交易（及paychanel=98）保持跟portal一致，暂时没有paychanel=49的交易
			+ " AND C.TXN_STA_CD IN ('30000','30101') AND ROUND(TO_NUMBER(SYSDATE - (C.CREATED_TS+0))) >= 10)";

	@Override
	public List<Object> findTimeoutCpsGenMainOrderByTimeSpan(final int timeSpan) {
		Map m = new HashMap();
		m.put("timeSpan", timeSpan);
		List<?> result = findBySql(FIND_TIMEOUT_ORDER_BY_TIMESPAN_SQL, m);
		return (List<Object>) result;

	}

	

	/**
	 * 虚拟账户充值超时订单查询
	 */
	@Override
	public List<Object> findTimeoutOrdersInRecharge(Map param, int pageNo,
			int pageSize) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00500'")
				.append(" and (((PAY_CHANNEL in ('01','03') or PAY_CHANNEL is null)");
		String b2bTimeSpan = String.valueOf(param.get("b2b"));
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
		sql.append(" or (PAY_CHANNEL in ('51') " + getLifeCycleSql(b2bTimeSpan)
				+ "b2bTimeSpan))");

		Map m = new HashMap();
		m.put("b2cTimeSpan", b2cTimeSpan.replaceAll("[a-zA-Z]+", ""));
		m.put("b2bTimeSpan", b2bTimeSpan.replaceAll("[a-zA-Z]+", ""));
		List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
		return (List<Object>) result;
	}

	private String getLifeCycleSql(String timeSpan) {
		String str = "";
		if (timeSpan.endsWith("d")) {
			str = " and TRUNC(TO_NUMBER(SYSDATE - (CREATED_TS+0)))>=:";
		} else {
			str = " and TRUNC(TO_NUMBER(SYSDATE - (CREATED_TS+0))*24*60)>=:";
		}

		return str;
	}


	/**
	 * 线下充值超时订单查询
	 */
	@Override
	public List<Object> findTimeoutOrdersInOfflineRecharge(Map param,
														   int pageNo, int pageSize) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00500'")
				.append("and PAY_CHANNEL in ('49','98') ");
		String timeSpan = String.valueOf(param.get("timeSpan"));
		sql.append(getLifeCycleSql(timeSpan) + "timeSpan");
		String time = timeSpan.replaceAll("[a-zA-Z]+", "");
		Map m = new HashMap();
		m.put("timeSpan", time);
		List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
		return (List<Object>) result;
	}

	/**
	 * 大宗入金超时订单查询	 
	 */
	@Override
	public List<Object> findTimeoutOrdersInMoneyIn(Map param, int pageNo,
												   int pageSize) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00200'")
				.append("and (PAY_CHANNEL in ('01','02','03','04','06','51','52') or PAY_CHANNEL is null)");
		String timeSpan = String.valueOf(param.get("timeSpan"));
		sql.append(getLifeCycleSql(timeSpan) + "timeSpan");
		String time = timeSpan.replaceAll("[a-zA-Z]+", "");
		Map m = new HashMap();
		m.put("timeSpan", time);
		List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
		return (List<Object>) result;
	}


	/**
	 * 担保支付申请超时订单查询
	 */
	@Override
	public List<Object> findTimeoutOrdersInGuaranteePaymentApply(Map param,
																 int pageNo, int pageSize) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00400'")
				.append(" and (((PAY_CHANNEL in ('01','02','03','04','06') or PAY_CHANNEL is null)");
		String b2bTimeSpan = String.valueOf(param.get("b2b"));
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
		sql.append(" or (PAY_CHANNEL in ('51','52') "
				+ getLifeCycleSql(b2bTimeSpan) + "b2bTimeSpan))");
		Map m = new HashMap();
		m.put("b2cTimeSpan",
				String.valueOf(param.get("b2c")).replaceAll("[a-zA-Z]+", ""));
		m.put("b2bTimeSpan",
				String.valueOf(param.get("b2b")).replaceAll("[a-zA-Z]+", ""));
		List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
		return (List<Object>) result;
	}

	/**
	 * 网关支付超时订单查询
	 */
	@Override
    public List<Object> findTimeoutOrdersInPay(Map param, int pageNo,
                                               int pageSize) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00100'")
				.append(" and (((PAY_CHANNEL in ('01','02','03','04','06','08','30','31','32','16','17','18') or PAY_CHANNEL is null)");
		String b2bTimeSpan = String.valueOf(param.get("b2b"));
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
		sql.append(" or (PAY_CHANNEL in ('51','52') "
				+ getLifeCycleSql(b2bTimeSpan) + "b2bTimeSpan))");
		Map m = new HashMap();
		m.put("b2cTimeSpan",
				String.valueOf(param.get("b2c")).replaceAll("[a-zA-Z]+", ""));
		m.put("b2bTimeSpan",
				String.valueOf(param.get("b2b")).replaceAll("[a-zA-Z]+", ""));
		List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
		return (List<Object>) result;
	}

	@Override
	public List<Object> findTimeoutOrdersInOrder01813(Map param, int pageNo, int pageSize) {
		StringBuffer sql = new StringBuffer("select t.gopay_order_id from CPS_GEN_MAIN_ORDER t LEFT JOIN BIS_BANK_MAIN_ORDER b ON t.gopay_order_id = b.gopay_order_id")
				.append(" where (t.TXN_STA_CD in ('30000','30101') OR (t.TXN_STA_CD = '90101' AND b.order_status in (91,99)))");

		sql.append(" and t.GOPAY_INT_TXN_CD='01813'")
				.append(" and (((t.PAY_CHANNEL in ('01','02','03','04','06','08','30','31','32','16','17','18') or t.PAY_CHANNEL is null)");
		String b2bTimeSpan = String.valueOf(param.get("b2b"));
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql01813(b2cTimeSpan) + "b2cTimeSpan)");
		sql.append(" or (t.PAY_CHANNEL in ('51','52') "
				+ getLifeCycleSql01813(b2bTimeSpan) + "b2bTimeSpan))");
		Map m = new HashMap();
		m.put("b2cTimeSpan",
				String.valueOf(param.get("b2c")).replaceAll("[a-zA-Z]+", ""));
		m.put("b2bTimeSpan",
				String.valueOf(param.get("b2b")).replaceAll("[a-zA-Z]+", ""));
		//logger.error("<FOR DEBUG> sql:"+sql.toString());
		List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
		return (List<Object>) result;
	}

	@Override
	public int findTimeoutOrdersInFundCount(Map param) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD in ('02019','02004','02013')")
				.append(" and ((PAY_CHANNEL in ('02','03','06','52') or PAY_CHANNEL is null)");
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");

		Map m = new HashMap();
		m.put("b2cTimeSpan", b2cTimeSpan.replaceAll("[a-zA-Z]+", ""));
		logger.info("基金订单清理数量sql:"+sql+",参数："+ FastJsonUtil.toJSONString(m));
		int count = findCountBySql(sql.toString(), m);
		return count;
	}

	@Override
	public List<Object> findTimeoutOrdersInFund(Map param, int pageNo, int pageSize) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD in ('02019','02004','02013')")
				.append(" and ((PAY_CHANNEL in ('02','03','06','52') or PAY_CHANNEL is null)");
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
		Map m = new HashMap();
		m.put("b2cTimeSpan", b2cTimeSpan.replaceAll("[a-zA-Z]+", ""));
		logger.info("基金订单清理sql:"+sql+",参数："+ FastJsonUtil.toJSONString(m));
		List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
		return (List<Object>) result;
	}

	private String getLifeCycleSql01813(String timeSpan) {
		String str = "";
		if (timeSpan.endsWith("d")) {
			str = " and TRUNC(TO_NUMBER(SYSDATE - (t.CREATED_TS+0)))>=:";
		} else {
			str = " and TRUNC(TO_NUMBER(SYSDATE - (t.CREATED_TS+0))*24*60)>=:";
		}

		return str;
	}

	private String getBaseSql() {
		StringBuffer sql = new StringBuffer(
				"select gopay_order_id from CPS_GEN_MAIN_ORDER")
				.append(" where TXN_STA_CD in('"
						+ String.valueOf(TxnStaCode.TXN_STA_CD_30000.value)
						+ "','"
						+ String.valueOf(TxnStaCode.TXN_STA_CD_30101.value)
						+ "')");
		return sql.toString();
	}

	// 根据对账次数获取银行订单中的超时订单
	/*
	 * @Override public List<CpsGenMainOrder>
	 * findTimeoutCpsGenMainOrderByTotalCheckTimes( int totalCheckTimes) { //
	 * TODO Auto-generated method stub
	 * 
	 * String hqlTotalCheckTimes =
	 * " FROM CpsGenMainOrder c  WHERE c.gopayOrderId IN (SELECT  b.orderId  from BankOrder b WHERE b.orderId = c.gopayOrderId AND b.totalCheckTimes >= :totalCheckTimes )"
	 * + "  AND c.txnStaCd = '" + TxnStaCode.TXN_STA_CD_30000.value + "'";
	 * Map<String, Object> paramTotalCheckTimes = new HashMap<String, Object>();
	 * paramTotalCheckTimes.put("totalCheckTimes", totalCheckTimes);
	 * 
	 * return findByHql(hqlTotalCheckTimes, paramTotalCheckTimes); }
	 */
	@Override
	public CpsGenMainOrder findByRelevanceGopayOrderId(
			String relevanceGopayOutOrderId) {
		String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId";
		Map params = new HashMap();
		params.put("RelevanceGopayOutOrderId", relevanceGopayOutOrderId);

		List<CpsGenMainOrder> res = super.findByHql(hql, params);
		if (res != null && res.size() > 0) {
			return res.get(0);
		}
		return null;
	}
	
	@Override
	public CpsGenMainOrder findByRelevanceGopayOrderId01609(String relevanceGopayOutOrderId) {
		String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId and order.gopayIntTxnCd = :gopayIntTxnCd";
		Map params = new HashMap();
		params.put("RelevanceGopayOutOrderId", relevanceGopayOutOrderId);
		params.put("gopayIntTxnCd", "01609");
		List<CpsGenMainOrder> res = super.findByHql(hql, params);
		if (res != null && res.size() > 0) {
			return res.get(0);
		}
		return null;
	}
	@Override
	public CpsGenMainOrder findByRelevanceGopayOrderIdAndGopayIntTxnCd(
			String relevanceGopayOutOrderId ,String gopayIntTxnCd) {
		String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId and order.gopayIntTxnCd = :gopayIntTxnCd";
		Map params = new HashMap();
		params.put("RelevanceGopayOutOrderId", relevanceGopayOutOrderId);
		params.put("gopayIntTxnCd", gopayIntTxnCd);

		List<CpsGenMainOrder> res = super.findByHql(hql, params);
		if (res != null && res.size() > 0) {
			return res.get(0);
		}
		return null;
	}
	
	@Override
	public List<CpsGenMainOrder> findByRelevanceGopayOrderIds(
			String relevanceGopayOutOrderId) {
		String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId";
		Map params = new HashMap();
		params.put("RelevanceGopayOutOrderId", relevanceGopayOutOrderId);

		List<CpsGenMainOrder> res = super.findByHql(hql, params);
		if (res != null && res.size() > 0) {
			return res;
		}
		return null;
	}

	@Override
	public List<CpsGenMainOrder> getMainOrders(int txnStaCd, String gopayIntTxnCd) {
		String hql = "select main from CpsGenMainOrder main, CpsGenReceiptOrder recv  " +
				" where  main.gopayOrderId = recv.gopayOrderId and main.txnStaCd = :txnStaCd and " +
				"main.gopayIntTxnCd = :gopayIntTxnCd and recv.isRefund = :isRefund and main.createdTs > :date";
		Date date = new Date();
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		calendar.add(Calendar.DAY_OF_YEAR,-7);
		date = calendar.getTime();

		Map<String, Object> params = new HashMap<String, Object>();
		params.put("txnStaCd", txnStaCd);
		params.put("gopayIntTxnCd", gopayIntTxnCd);
		params.put("isRefund", RefundFlag.N);
		params.put("date", date);

		return this.findByHql(hql, params);
	}
	@Override
	public List<CpsGenMainOrder> getMainOrders(int txnStaCd, int pageIndex,
			int pageSize) {
		String hql = "from CpsGenMainOrder where txnStaCd = :txnStaCd";

		Map<String, Object> params = new HashMap<String, Object>();
		params.put("txnStaCd", txnStaCd);
    
		return this.findByHql(hql, params, pageIndex, pageSize);
	}
    @Override
    public List<CpsGenMainOrder> findByRelOrderId(String relOrderId) {

        String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId";
        Map params = new HashMap();
        params.put("RelevanceGopayOutOrderId", relOrderId);

        List<CpsGenMainOrder> res = super.findByHql(hql, params);
        
        return res;
    }

	@Override
	public int getMainOrderCount(int txnStaCd) {
		String hql = "select count(*) from CpsGenMainOrder where txnStaCd = :txnStaCd";

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("txnStaCd", txnStaCd);

		return this.findCountByHql(hql, params);
	}

	@Override
	public int findTimeoutOrdersInRechargeCount(Map param) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00500'")
				.append(" and (((PAY_CHANNEL in ('01','03') or PAY_CHANNEL is null)");
		String b2bTimeSpan = String.valueOf(param.get("b2b"));
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
		sql.append(" or (PAY_CHANNEL in ('51') " + getLifeCycleSql(b2bTimeSpan)
				+ "b2bTimeSpan))");

		Map m = new HashMap();
		m.put("b2cTimeSpan", b2cTimeSpan.replaceAll("[a-zA-Z]+", ""));
		m.put("b2bTimeSpan", b2bTimeSpan.replaceAll("[a-zA-Z]+", ""));
		int count = findCountBySql(sql.toString(), m);
		return count;
	}


	@Override
	public int findTimeoutOrdersInPayCount(Map param) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00100'")
				.append(" and (((PAY_CHANNEL in ('01','02','03','04','06','30','31','32','16','17','18') or PAY_CHANNEL is null)");
		String b2bTimeSpan = String.valueOf(param.get("b2b"));
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
		sql.append(" or (PAY_CHANNEL in ('51','52') "
				+ getLifeCycleSql(b2bTimeSpan) + "b2bTimeSpan))");
		Map m = new HashMap();
		m.put("b2cTimeSpan",
				String.valueOf(param.get("b2c")).replaceAll("[a-zA-Z]+", ""));
		m.put("b2bTimeSpan",
				String.valueOf(param.get("b2b")).replaceAll("[a-zA-Z]+", ""));
		int count = findCountBySql(sql.toString(), m);
		return count;
	}

	@Override
	public List<Object> findTimeoutManualAdjust(int pageIndex, int pageSize) {
		String FIND_TIMEOUT_ORDER_BY_TIMESPAN_SQL = "SELECT  c.gopay_order_id  FROM CPS_GEN_MAIN_ORDER C "
				+ "WHERE(C.GOPAY_INT_TXN_CD in ('09800') and C.TXN_STA_CD IN (30000,30101)"
				+ "AND to_char(cast(C.CREATED_TS as date),'YYYYMMDD') = to_char(sysdate-1,'YYYYMMDD'))";

		Map m = new HashMap();
		List<?> result = findBySql(FIND_TIMEOUT_ORDER_BY_TIMESPAN_SQL,
				pageIndex, pageSize);
		return (List<Object>) result;
	}

	@Override
	public int findTimeoutManualAdjustCount() {
		String FIND_TIMEOUT_ORDER_BY_TIMESPAN_SQL = "SELECT  c.gopay_order_id  FROM CPS_GEN_MAIN_ORDER C "
				+ "WHERE(C.GOPAY_INT_TXN_CD in ('09800') and C.TXN_STA_CD IN (30000,30101)"
				+ "AND to_char(cast(C.CREATED_TS as date),'YYYYMMDD') = to_char(sysdate-1,'YYYYMMDD'))";

		Map m = new HashMap();
		int count = findCountBySql(FIND_TIMEOUT_ORDER_BY_TIMESPAN_SQL, m);
		return count;
	}

	@Override
	public int findTimeoutOrdersInOfflineRechargeCount(Map param) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00500'")
				.append("and PAY_CHANNEL in ('49','98') ");
		String timeSpan = String.valueOf(param.get("timeSpan"));
		sql.append(getLifeCycleSql(timeSpan) + "timeSpan");
		String time = timeSpan.replaceAll("[a-zA-Z]+", "");
		Map m = new HashMap();
		m.put("timeSpan", time);
		int count = findCountBySql(sql.toString(), m);
		return count;
	}

	@Override
	public int findTimeoutOrdersInMoneyInCount(Map param) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00200'")
				.append("and (PAY_CHANNEL in ('01','02','03','04','06','51','52') or PAY_CHANNEL is null)");
		String timeSpan = String.valueOf(param.get("timeSpan"));
		sql.append(getLifeCycleSql(timeSpan) + "timeSpan");
		String time = timeSpan.replaceAll("[a-zA-Z]+", "");
		Map m = new HashMap();
		m.put("timeSpan", time);
		int count = findCountBySql(sql.toString(), m);
		return count;
	}

	@Override
	public int findTimeoutOrdersInGuaranteePaymentApplyCount(Map param) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD='00400'")
				.append(" and (((PAY_CHANNEL in ('01','02','03','04','06') or PAY_CHANNEL is null)");
		String b2bTimeSpan = String.valueOf(param.get("b2b"));
		String b2cTimeSpan = String.valueOf(param.get("b2c"));
		sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
		sql.append(" or (PAY_CHANNEL in ('51','52') "
				+ getLifeCycleSql(b2bTimeSpan) + "b2bTimeSpan))");
		Map m = new HashMap();
		m.put("b2cTimeSpan",
				String.valueOf(param.get("b2c")).replaceAll("[a-zA-Z]+", ""));
		m.put("b2bTimeSpan",
				String.valueOf(param.get("b2b")).replaceAll("[a-zA-Z]+", ""));
		int count = findCountBySql(sql.toString(), m);
		return count;
	}

	@Override
	public List<?> findSuOrdersWithOutInAcct(int pageIndex, int pageSize,
			Date date) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT O.GOPAY_ORDER_ID ")
				.append("FROM CPS_GEN_MAIN_ORDER O,BIS_BANK_MAIN_ORDER B ")
				.append("WHERE (O.GOPAY_ORDER_ID NOT IN (SELECT D.GOPAY_ORDER_ID FROM ACPS_ACCT_TRANS_DTL D) ")
				.append("AND (O.TXN_STA_CD='20000' or O.TXN_STA_CD='30102') AND B.ORDER_STATUS IN ('00','02','03','04') AND O.GOPAY_ORDER_ID=B.GOPAY_ORDER_ID) ")
				.append("AND O.TRAN_FINISH_TIME<=:time AND O.TRAN_FINISH_TIME>:startTime");
		Map map = new HashMap();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<?> result = new ArrayList();
		try {
			Date startTime = sdf.parse("2014-06-09 00:00:00");

			map.put("time", date);
			map.put("startTime", startTime);
			result = findBySql(sql.toString(), map, pageIndex, pageSize);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int findSuOrdersWithOutInAcctCount(Date date) {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT count(O.GOPAY_ORDER_ID) ")
				.append("FROM CPS_GEN_MAIN_ORDER O,BIS_BANK_MAIN_ORDER B ")
				.append("WHERE (O.GOPAY_ORDER_ID NOT IN (SELECT D.GOPAY_ORDER_ID FROM ACPS_ACCT_TRANS_DTL D) ")
				.append("AND (O.TXN_STA_CD='20000' or O.TXN_STA_CD='30102') AND B.ORDER_STATUS IN ('00','02','03','04') AND O.GOPAY_ORDER_ID=B.GOPAY_ORDER_ID) ")
				.append("AND O.TRAN_FINISH_TIME<=:time AND O.TRAN_FINISH_TIME>:startTime");
		Map map = new HashMap();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int count = 0;
		try {
			Date startTime = sdf.parse("2014-06-09 00:00:00");
			map.put("time", date);
			map.put("startTime", startTime);
			count = findCountBySql(sql.toString(), map);
			return count;
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int findTimeoutOrdersCount(Map param) {
		StringBuffer sql = new StringBuffer(getBaseSql()
				+ " and GOPAY_INT_TXN_CD=:transType");
		String b2bTimeSpan = (String) param.get("b2b");
		String b2cTimeSpan = (String) param.get("b2c");
	    sql.append(" and(");
		Map m = new HashMap();
		if(b2cTimeSpan!=null){
			sql.append("((PAY_CHANNEL in (:b2cChannels) or PAY_CHANNEL is null)");
			sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
			m.put("b2cChannels", param.get("b2cChannels"));
			m.put("b2cTimeSpan", String.valueOf(param.get("b2c")).replaceAll("[a-zA-Z]+", ""));
		}
		if(b2bTimeSpan!=null&&b2cTimeSpan!=null){
			sql.append(" or ");
		}
		if(b2bTimeSpan!=null){
			sql.append(" (PAY_CHANNEL in (:b2bChannels) "
					+ getLifeCycleSql(b2bTimeSpan) + "b2bTimeSpan)");
			m.put("b2bChannels", param.get("b2bChannels"));
			m.put("b2bTimeSpan", String.valueOf(param.get("b2b")).replaceAll("[a-zA-Z]+", ""));
		}
		 sql.append(" )");
		m.put("transType", param.get("transType"));
		
		int count = findCountBySql(sql.toString(), m);
		return count;
	}
	 @Override
     public List<Object> findTimeoutOrders(Map param, int pageNo, int pageSize){
		 StringBuffer sql = new StringBuffer(getBaseSql()
					+ " and GOPAY_INT_TXN_CD=:transType");
			String b2bTimeSpan = String.valueOf(param.get("b2b"));
			String b2cTimeSpan = String.valueOf(param.get("b2c"));
		    sql.append(" and(");
			Map m = new HashMap();
			if(b2cTimeSpan!=null){
				sql.append("((PAY_CHANNEL in (:b2cChannels) or PAY_CHANNEL is null)");
				sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
				m.put("b2cChannels", param.get("b2cChannels"));
				m.put("b2cTimeSpan", String.valueOf(param.get("b2c")).replaceAll("[a-zA-Z]+", ""));
			}
			if(b2bTimeSpan!=null&&b2cTimeSpan!=null){
				sql.append(" or ");
			}
			if(b2bTimeSpan!=null){
				sql.append(" (PAY_CHANNEL in (:b2bChannels) "
						+ getLifeCycleSql(b2bTimeSpan) + "b2bTimeSpan)");
				m.put("b2bChannels", param.get("b2bChannels"));
				m.put("b2bTimeSpan", String.valueOf(param.get("b2b")).replaceAll("[a-zA-Z]+", ""));
			}
			 sql.append(" )");
			m.put("transType", param.get("transType"));
			
			List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
			return (List<Object>) result;
	 }
	 
	 	   
	    @Override
	    public List<CpsGenMainOrder> findCpsGenMainOrders(String payAcct, String gopayTxnCt,int pageIndex, int pageSize) {
	        String queryHql = " from CpsGenMainOrder t where  1=1";// t.PAY_ACCT ='" + payAcct + "'";
	        StringBuffer hql= new StringBuffer(queryHql);
	       if(StringUtils.isNotBlank(payAcct)){
	           hql.append(" and t.payAcct='").append(payAcct.trim()).append("'");
	       }
	       if(StringUtils.isNotBlank(gopayTxnCt)){
	           hql.append(" and t.gopayIntTxnCd='").append(gopayTxnCt.trim()).append("'");
	       }
	       hql.append(" and not exists ( from CpsGenMainOrder t2 where t2.relevanceGopayOrderId=t.gopayOrderId)");
	        return findByHql(hql.toString(), null, pageIndex, pageSize);
	    }


	   
	    @Override
	    public int cpsGenMainOrderCount(String payAcct, String gopayTxnCt) {
	        String querySql = " select count(1) from CPS_GEN_MAIN_ORDER t where  1=1";// t.PAY_ACCT ='" + payAcct + "'";
	        StringBuffer sql= new StringBuffer(querySql);
	       if(StringUtils.isNotBlank(payAcct)){
	           sql.append(" and t.PAY_ACCT='").append(payAcct.trim()).append("'");
	       }
	       if(StringUtils.isNotBlank(gopayTxnCt)){
	           sql.append(" and t.GOPAY_INT_TXN_CD='").append(gopayTxnCt.trim()).append("'");
	       }
	       sql.append(" and not exists (select * from CPS_GEN_MAIN_ORDER t2 where t2.RELEVANCE_GOPAY_ORDER_ID=t.GOPAY_ORDER_ID)");
	        return findCountBySql(sql.toString());
	    }
	    //------------------------------------------放到cron中了 modify by zhoupeng 20170606 --start---//
//		@Override
//		public List<CpsGenMainOrder> findTimeoutOrdersByCreateTimeAndProcessCode(String txnStaCd) {
//			String hql = " from CpsGenMainOrder t where  1=1 AND txnStaCd = :txnStaCd AND gatewaySource in (:gatewaySource) AND "
//					   + " (TO_CHAR(systimestamp, 'yyyymmddhh24miss')-TO_CHAR(createdTs + 1, 'yyyymmddhh24miss')) > 0 ";
////						+ " (TO_CHAR(systimestamp, 'yyyymmddhh24miss')-TO_CHAR(createdTs + 1/480, 'yyyymmddhh24miss')) > 0 ";//fordebug
//			Map<String, Object> param = new HashMap<String, Object>();
//			param.put("txnStaCd", Integer.parseInt(txnStaCd));
//			String[] gatewaySource={GatewaySource.GATEWAY_SOURCE_05.value,GatewaySource.GATEWAY_SOURCE_08.value} ;
//			param.put("gatewaySource",gatewaySource);
//			logger.info("updateMainOrderStatusJob sql: " + hql + ",txnStaCd="+Integer.parseInt(txnStaCd)+",gatewaySource="+GatewaySource.GATEWAY_SOURCE_05.value);
//			List<CpsGenMainOrder> list = findByHql(hql, param);
//			return list;
//		}
//		@Override
//		public List<CpsGenMainOrder>  findTimeoutBounsOrdersByCreateTime(String gopayOrderId){
//			String hql = " from CpsGenMainOrder t where  1=1  AND gopayOrderId in (:array) AND "
//					   + " (TO_CHAR(systimestamp, 'yyyymmddhh24miss')-TO_CHAR(createdTs + 1, 'yyyymmddhh24miss')) > 0 ";
////						+ " (TO_CHAR(systimestamp, 'yyyymmddhh24miss')-TO_CHAR(createdTs + 1/480, 'yyyymmddhh24miss')) > 0 ";//fordebug
//			Map<String, Object> param = new HashMap<String, Object>();
//			String[] array=gopayOrderId.split(",");//割裂字符串转数组
//			param.put("array",array);
//			logger.info("updateMainOrderStatusJob sql: " + hql + ",gopayOrderId="+gopayOrderId);
//			List<CpsGenMainOrder> list = findByHql(hql, param);
//			return list;
//		}
//
//        @Override
//        public List<CpsGenMainOrder> findTimeoutTyyOrdersByCreateTimeAndProcessCode(String txnStaCd) {
//            StringBuffer sb=new StringBuffer();
//            sb.append(" from CpsGenMainOrder ");
//            sb.append(" where  1=1 ");
//            sb.append(" AND txnStaCd = :txnStaCd ");
//            sb.append(" AND gatewaySource = :gatewaySource ");
//            sb.append(" AND gopayIntTxnCd = :gopayIntTxnCd ");
//            sb.append(" AND (TO_CHAR(systimestamp, 'yyyymmddhh24miss')-TO_CHAR(createdTs +1/48 , 'yyyymmddhh24miss')) > 0 ");//半小时   
//            Map<String, Object> param = new HashMap<String, Object>();
//            param.put("txnStaCd", Integer.parseInt(txnStaCd));
//            param.put("gatewaySource",GatewaySource.GATEWAY_SOURCE_05.value);
//            param.put("gopayIntTxnCd",IntTxnCd._01615.value);
//            logger.info("updateTyyMainOrderStatusJob sql: "+sb.toString());
//            List<CpsGenMainOrder> list = findByHql(sb.toString(), param);
//            return list;
//        }
//        @Override
//        public List<CpsGenMainOrder> findBankTimeoutTyyOrdersByCreateTimeAndProcessCode(List<Integer> txnStaCodeList) {
//            StringBuffer sb=new StringBuffer();
//            sb.append(" from CpsGenMainOrder ");
//            sb.append(" where  1=1 ");
//            sb.append(" AND txnStaCd in (:txnStaCds) ");
//            sb.append(" AND gatewaySource = :gatewaySource ");
//            sb.append(" AND gopayIntTxnCd = :gopayIntTxnCd ");
//            sb.append(" AND  (TO_CHAR(systimestamp, 'yyyymmddhh24miss')-TO_CHAR(createdTs + 1, 'yyyymmddhh24miss')) > 0  ");//00000000003000   
//            Map<String, Object> param = new HashMap<String, Object>();
//            param.put("txnStaCds", txnStaCodeList.toArray());
//            param.put("gatewaySource",GatewaySource.GATEWAY_SOURCE_05.value);
//            param.put("gopayIntTxnCd",IntTxnCd._01615.value);
//            logger.info("updateTyyMainOrderStatusJob sql: "+sb.toString());
//            List<CpsGenMainOrder> list = findByHql(sb.toString(), param);
//            return list;
//        }
	  //------------------------------------------放到cron中了 modify by zhoupeng 20170606 --end---//
	@Override
	public boolean findRefundExistByRelevanceId(String orderId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select 1 from cps_gen_main_order c where 1=1 ");
		sql.append(" and (c.remark2='银行卡异常' or c.remark2='请核对账户信息')");
		sql.append(" and not (c.txn_sta_cd=20000 or (c.txn_sta_cd>=30000 and c.txn_sta_cd<40000))");
		sql.append(" and c.gopay_int_txn_cd in ('01203','00701','01200') ");
		sql.append(" and c.relevance_gopay_order_id=:orderId");
		sql.append(" and rownum=1");
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("orderId",orderId);
		long start = System.currentTimeMillis();
		List rl=this.findBySql(sql.toString(),param);
		boolean result =  (rl==null||rl.size()==0) ? false : true;
		logger.info("------sql:" + sql.toString() + ",查询因银行卡异常或账户信息错误导致退款失败的订单数量耗时:{}ms", (System.currentTimeMillis() - start));
		return result;
	}

	@Override
	public boolean findExistByIntTxnCdCustId(String tranType, String custId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select 1 from CPS_GEN_MAIN_ORDER c where 1 = 1");
		sql.append(" and c.txn_sta_cd = 20000");
		sql.append(" and c.PAY_CUST_ID = :custId");
		sql.append(" and c.GOPAY_INT_TXN_CD = :tranType");
		sql.append(" and rownum=1");
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("custId",custId);
		param.put("tranType",tranType);
		long start = System.currentTimeMillis();
		List rl=this.findBySql(sql.toString(),param);
		boolean result =  (rl==null||rl.size()==0) ? false : true;
		logger.info("------sql:" + sql.toString() + ",查询商户"+custId+"是否存在"+tranType+"订单，耗时:{}ms", (System.currentTimeMillis() - start));
		return result;
	}

	@Override
		public CpsGenMainOrder findByRelevanceAndTxnCd(
				String relevanceGopayOutOrderId,String intTxnCd) {
			String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId and order.gopayIntTxnCd=:gopayIntTxnCd ";
			Map params = new HashMap();
			params.put("RelevanceGopayOutOrderId", relevanceGopayOutOrderId);
			params.put("gopayIntTxnCd", intTxnCd);

			List<CpsGenMainOrder> res = super.findByHql(hql, params);
			if (res != null && res.size() > 0) {
				return res.get(0);
			}
			return null;
		}
		
		/**
		 * 根据国付宝交易类型、活动类型查询未参加过活动的订单
		 * @param gopayIntTxnCd
		 * @return
		 */
		@Override
		public List<CpsGenMainOrder> findByWaPromotionsLog(List<String> intTxnCdList, Map<String,String> paramMap){
			Map<String, Object> param = new HashMap<String, Object>();
			StringBuffer sql = new StringBuffer();
			StringBuffer existsSql = new StringBuffer();
			sql.append("from CpsGenMainOrder t where  t.txnStaCd='20000' and (") ;
			existsSql.append(" and not exists (select m from WaPromotionsLog m where m.condition1 = t.gopayOrderId and ( ") ;
		    Iterator<String> it = intTxnCdList.iterator(); 
		    while (it.hasNext()) {
		    	String gopayIntTxnCd = it.next();  
				if(IntTxnCd._01611.value.equals(gopayIntTxnCd)){
					sql.append(" (t.gopayIntTxnCd =:intTxnCd01611 and t.merTxnAmt>=:merTxnAmt01611 and t.createdTs >= to_timestamp(:startTime01611 , 'yyyy-MM-dd HH24:MI:ss.ff') AND t.createdTs <= to_timestamp(:endTime01611 , 'yyyy-MM-dd HH24:MI:ss.ff')) ");
					existsSql.append(" m.type =:type4 ");
					param.put("intTxnCd01611", "01611");//交易码
					param.put("merTxnAmt01611", new BigDecimal(paramMap.get("tel_recharge_mer_txn_amt")));//订单金额
					param.put("type4", "4");//活动类型
					param.put("startTime01611", paramMap.get("tel_recharge_bouns_start_time"));//活动开始时间
					param.put("endTime01611", paramMap.get("tel_recharge_bouns_end_time"));//活动结束时间
				}
				if(IntTxnCd._01612.value.equals(gopayIntTxnCd)){
					sql.append(" (t.gopayIntTxnCd =:intTxnCd01612 and t.merTxnAmt>=:merTxnAmt01612 and t.createdTs >= to_timestamp(:startTime01612 , 'yyyy-MM-dd HH24:MI:ss.ff') AND t.createdTs <= to_timestamp(:endTime01612 , 'yyyy-MM-dd HH24:MI:ss.ff')) ");
					existsSql.append(" m.type =:type5 ");
					param.put("intTxnCd01612", "01612");
					param.put("merTxnAmt01612", new BigDecimal(paramMap.get("flow_recharge_mer_txn_amt")));
					param.put("type5", "5");
					param.put("startTime01612", paramMap.get("flow_recharge_bouns_start_time"));
					param.put("endTime01612", paramMap.get("flow_recharge_bouns_end_time"));
					
				}
				if(IntTxnCd._01613.value.equals(gopayIntTxnCd)){
					sql.append(" (t.gopayIntTxnCd =:intTxnCd01613 and t.merTxnAmt>=:merTxnAmt01613 and t.createdTs >= to_timestamp(:startTime01613 , 'yyyy-MM-dd HH24:MI:ss.ff') AND t.createdTs <= to_timestamp(:endTime01613 , 'yyyy-MM-dd HH24:MI:ss.ff')) ");
					existsSql.append(" m.type =:type6 ");
					param.put("intTxnCd01613", "01613");
					param.put("merTxnAmt01613", new BigDecimal(paramMap.get("fuel_recharge_mer_txn_amt")));
					param.put("type6", "6");
					param.put("startTime01613", paramMap.get("fuel_recharge_bouns_start_time"));
					param.put("endTime01613", paramMap.get("fuel_recharge_bouns_end_time"));
				}
				if(it.hasNext()){
					sql.append(" or ");
					existsSql.append(" or ");
				}else{
					sql.append(" ) ");
					existsSql.append(" ) ");
				}
			}
		    existsSql.append(" ) ");
			sql.append(existsSql);
			logger.info("------sql:"+sql.toString()+",param:"+param.toString());
			long start = System.currentTimeMillis();
			 List<CpsGenMainOrder> resultList = this.findByHql(sql.toString(), param);
			logger.info("------查询符合条件的订单耗时:{}ms", (System.currentTimeMillis() - start));
			return resultList;
		}


		/**
	     * 根据交易类型及、收款账户或付款账户、状态（可以多个）查询大于创建时间的订单数量
	     * @param gopayIntTxnCd 交易类型
	     * @param custId 收款账户或付款账户
	     * @param custIdType 账户类型 pay：付款账户，recv：收款账户,其他字符串sql无custId查询条件
	     * @param txnStaCds 订单状态
	     * @param txnStaCdsEqualFlag 订单状态标识 true：等于，false：不等于
	     * @param createdTs 创建时间
	     * @return 订单数量
	     * @author tangyang
	     * @date 20160911
	     * @使用范围：手机钱包充话费、充流量、充加油卡，校验当日交易次数限制
	     */
		@Override
		public int findCountByIntTxnCdAndCustIdAndTxnStaCd(String gopayIntTxnCd, String custId, String custIdType,
				int[] txnStaCds, boolean txnStaCdsEqualFlag, String createdTs ) {
			// TODO Auto-generated method stub
			StringBuffer sql = new StringBuffer();
			Map<String, Object> param = new HashMap<String, Object>();
			sql.append("select count(1) from CPS_GEN_MAIN_ORDER c ");
			//add by leixiaobao 关联查询业务订单,因为业务相关的状态都放到业务表中了
			sql.append(" left join wa_prepaid_order_dtl p on c.gopay_order_id=p.gopay_order_id ");
			sql.append(" where 1 = 1");
			if(WalletConstants.TRANS_CUST_TYPE_PAY.equals(custIdType)){
				sql.append("and c.PAY_CUST_ID = :custId ");
			}else if (WalletConstants.TRANS_CUST_TYPE_RECV.equals(custIdType)){
				sql.append("and c.MAIN_RECV_CUST_ID = :custId ");
			}
			sql.append("and c.GOPAY_INT_TXN_CD = :gopayIntTxnCd ");
			
			if(txnStaCds.length>0 && txnStaCdsEqualFlag){
				StringBuffer txnStaCdStr = new StringBuffer();
				for(int i = 0 ;i< txnStaCds.length ; i++){
					txnStaCdStr.append(txnStaCds[i] + ",") ;
				}
				sql.append("and p.recharge_status in (" + txnStaCdStr.toString().substring(0,txnStaCdStr.toString().length()-1) + ") ");
//			    sql.append("and c.TXN_STA_CD in ( :txnStaCds ) ");
			}else if (txnStaCds.length>0 && !txnStaCdsEqualFlag){
				StringBuffer txnStaCdStr = new StringBuffer();
				for(int i = 0 ;i< txnStaCds.length ; i++){
					txnStaCdStr.append(txnStaCds[i] + ",") ;
				}
				sql.append("and p.recharge_status not in (" + txnStaCdStr.toString().substring(0,txnStaCdStr.toString().length()-1) + ") ");
//			    sql.append("and c.TXN_STA_CD not in ( :txnStaCds ) ");
			}
			if(StringUtils.isNotBlank(createdTs)){
				sql.append("and c.CREATED_TS >= to_date( :createdTs , 'yyyyMMdd') ");
			}
			param.put("custId", custId);
			param.put("gopayIntTxnCd", gopayIntTxnCd);
//			param.put("txnStaCds", txnStaCds);
			param.put("createdTs", createdTs);
			logger.info("------sql=" + sql.toString() + ",param=" + param.toString());
	        long start = System.currentTimeMillis();
			int count =  this.findCountBySql(sql.toString(),param);
			logger.info("查询订单数量耗时:{}ms,查询结果:{}", (System.currentTimeMillis() - start),count);
			return count;
		}
		
		/**
		 * 
	     * 根据交易类型及、收款账户或付款账户、状态（可以多个）查询大于创建时间的订单数量
	     * @param gopayIntTxnCd 交易类型
	     * @param custId 收款账户
	     * @param txnStaCds 订单状态
	     * @param txnStaCdsEqualFlag 订单状态标识 true：等于，false：不等于
	     * @param createdTs 创建时间
	     * @return 订单数量、商户交易总金额
	     * @author tangyang
	     * @date 20160911
	     * @使用范围：账户余额充值
	     * 因手机账户余额充值交易必须是RELEVANCE_GOPAY_ORDER_ID为空的交易才算真正的账户余额充值。所以不能使用上面方法。
	     * 手机钱包账户余额充值，校验当日交易次数限制
	     * 
	     */
		@Override
		public int findCountByIntTxnCdAndCustIdAndTxnStaCd(String gopayIntTxnCd, String mainRecvCustId,
				int[] txnStaCds, boolean txnStaCdsEqualFlag, String createdTs ) {
			// TODO Auto-generated method stub
			StringBuffer sql = new StringBuffer();
			Map<String, Object> param = new HashMap<String, Object>();
			sql.append("select count(1) from CPS_GEN_MAIN_ORDER c where 1=1 ");
			sql.append("and c.RELEVANCE_GOPAY_ORDER_ID is null ");
			sql.append("and c.MAIN_RECV_CUST_ID = :mainRecvCustId ");
			sql.append("and c.GOPAY_INT_TXN_CD = :gopayIntTxnCd  ");
			
			if(txnStaCds.length>0 && txnStaCdsEqualFlag){
				StringBuffer txnStaCdStr = new StringBuffer();
				for(int i = 0 ;i< txnStaCds.length ; i++){
					txnStaCdStr.append(txnStaCds[i] + ",") ;
				}
				sql.append("and c.TXN_STA_CD in (" + txnStaCdStr.toString().substring(0,txnStaCdStr.toString().length()-1) + ") ");
//			    sql.append("and c.TXN_STA_CD in ( :txnStaCds ) ");
			}else if (txnStaCds.length>0 && !txnStaCdsEqualFlag){
				StringBuffer txnStaCdStr = new StringBuffer();
				for(int i = 0 ;i< txnStaCds.length ; i++){
					txnStaCdStr.append(txnStaCds[i] + ",") ;
				}
				sql.append("and c.TXN_STA_CD not in (" + txnStaCdStr.toString().substring(0,txnStaCdStr.toString().length()-1) + ") ");
//			    sql.append("and c.TXN_STA_CD not in ( :txnStaCds) ");
			}
			if(StringUtils.isNotBlank(createdTs)){
				sql.append("and c.CREATED_TS >= to_date( :createdTs , 'yyyyMMdd') ");
			}
			param.put("mainRecvCustId", mainRecvCustId);
			param.put("gopayIntTxnCd", gopayIntTxnCd);
//			param.put("txnStaCds", txnStaCds);
			param.put("createdTs", createdTs);
			logger.info("------sql=" + sql.toString() + ",param=" + param.toString());
	        long start = System.currentTimeMillis();
	        int count =  this.findCountBySql(sql.toString(),param);
	        logger.info("查询订单数量耗时:{}ms,查询结果:{}", (System.currentTimeMillis() - start),count);
		    return count;
		}
		
		/**
	     * 根据交易类型及、付款账户/收款账户、状态（可以多个）查询大于创建时间的订单数量,及总金额
	     * @param gopayIntTxnCd 交易类型
	     * @param custId 收款账户或付款账户
	     * @param custIdType 账户类型 pay：付款账户，recv：收款账户,其他字符串sql无custId查询条件
	     * @param txnStaCds 订单状态
	     * @param txnStaCdsEqualFlag 订单状态标识 true：等于，false：不等于
	     * @param createdTs 创建时间
	     * @return 订单数量
	     * @author tangyang
	     * @date 20160927
	     * @使用范围：手机转账到国付宝01603、发红包01608、我要收款01607、转账到银行卡01605
	     */
		@Override
		public Map findCountAndTotalAmt(String gopayIntTxnCd, String custId, String custIdType,
				int[] txnStaCds, boolean txnStaCdsEqualFlag, String createdTs ) {
			// TODO Auto-generated method stub
			StringBuffer sql = new StringBuffer();
			Map<String, Object> param = new HashMap<String, Object>();
			sql.append("select count(1),sum(c.mer_txn_amt) from CPS_GEN_MAIN_ORDER c where 1 = 1 ");
			if(WalletConstants.TRANS_CUST_TYPE_PAY.equals(custIdType)){
			    sql.append("and c.PAY_CUST_ID = :custId ");
            }else if (WalletConstants.TRANS_CUST_TYPE_RECV.equals(custIdType)){
                sql.append("and c.MAIN_RECV_CUST_ID = :custId ");
            }
			sql.append("and c.GOPAY_INT_TXN_CD = :gopayIntTxnCd  ");
			
			if(txnStaCds.length>0 && txnStaCdsEqualFlag){
				StringBuffer txnStaCdStr = new StringBuffer();
				for(int i = 0 ;i< txnStaCds.length ; i++){
					txnStaCdStr.append(txnStaCds[i] + ",") ;
				}
				sql.append("and c.TXN_STA_CD in (" + txnStaCdStr.toString().substring(0,txnStaCdStr.toString().length()-1) + ") ");
//			    sql.append("and c.TXN_STA_CD in ( :txnStaCds ) ");
			}else if (txnStaCds.length>0 && !txnStaCdsEqualFlag){
				StringBuffer txnStaCdStr = new StringBuffer();
				for(int i = 0 ;i< txnStaCds.length ; i++){
					txnStaCdStr.append(txnStaCds[i] + ",") ;
				}
				sql.append("and c.TXN_STA_CD not in (" + txnStaCdStr.toString().substring(0,txnStaCdStr.toString().length()-1) + ") ");
//			    sql.append("and c.TXN_STA_CD not in ( :txnStaCds ) ");
			}
			if(StringUtils.isNotBlank(createdTs)){
				sql.append("and c.CREATED_TS >= to_date( :createdTs , 'yyyyMMdd') ");
			}
			
			param.put("custId", custId);
            param.put("gopayIntTxnCd", gopayIntTxnCd);
//            param.put("txnStaCds", txnStaCds);
            param.put("createdTs", createdTs);
            logger.info("------sql=" + sql.toString() + ",param=" + param.toString() );

	        long start = System.currentTimeMillis();
//			int count =  this.findCountBySql(sql.toString());
			List expendInfoList = this.findBySql(sql.toString(),param);
			logger.info("查询订单数量耗时:{}ms", (System.currentTimeMillis() - start));
			Object[] result = (Object[])expendInfoList.get(0);
			Integer count = new Integer(StringTools.checkNulltoString(result[0]));
			BigDecimal totalAmt = null;
			if(count.intValue() > 0){
				totalAmt = new BigDecimal(StringTools.checkNulltoString(result[1]));
			}else{
				totalAmt = new BigDecimal("0.00");
			}
			Map resultMap = new HashMap();
			resultMap.put("count", count);
			resultMap.put("totalAmt", totalAmt);
			return resultMap;
			
		}

		/**
         * 根据交易类型及、付款账户/收款账户、状态（可以多个）查询大于创建时间的订单数量,及总金额
         * @param gopayIntTxnCd 交易类型
         * @param custId 收款账户或付款账户
         * @param custIdType 账户类型 pay：付款账户，recv：收款账户,其他字符串sql无custId查询条件
         * @param txnStaCds 订单状态
         * @param txnStaCdsEqualFlag 订单状态标识 true：等于，false：不等于
         * @param bankCardName 持卡人名称
         * @param bankCardNameFalg 持卡人名称标识（用于查询本人卡和他人卡）
         * @param createdTs 创建时间
         * @return 订单数量
         * @author tangyang
         * @date 20170329
         * @使用范围：信用卡还款01606
         */
        @Override
        public Map findCountAndTotalAmt(String gopayIntTxnCd, String custId, String custIdType,
                int[] txnStaCds, boolean txnStaCdsEqualFlag,String bankCardName ,boolean bankCardNameFalg, String createdTs ) {
            // TODO Auto-generated method stub
            StringBuffer sql = new StringBuffer();
            Map<String, Object> param = new HashMap<String, Object>();
            sql.append("select count(1),sum(c.mer_txn_amt) from CPS_GEN_MAIN_ORDER c ");
            sql.append("left join cps_gen_pay_order pay on  pay.gopay_order_id = c.gopay_order_id ");
            sql.append("where 1 = 1 ");
            if(WalletConstants.TRANS_CUST_TYPE_PAY.equals(custIdType)){
                sql.append("and c.PAY_CUST_ID = :custId  ");
            }else if (WalletConstants.TRANS_CUST_TYPE_RECV.equals(custIdType)){
                sql.append("and c.MAIN_RECV_CUST_ID = :custId ");
            }
            sql.append("and c.GOPAY_INT_TXN_CD = :gopayIntTxnCd ");
            
            if(txnStaCds.length>0 && txnStaCdsEqualFlag){
                StringBuffer txnStaCdStr = new StringBuffer();
                for(int i = 0 ;i< txnStaCds.length ; i++){
                    txnStaCdStr.append(txnStaCds[i] + ",") ;
                }
                sql.append("and c.TXN_STA_CD in (" + txnStaCdStr.toString().substring(0,txnStaCdStr.toString().length()-1) + ") ");
//                sql.append("and c.TXN_STA_CD in ( :txnStaCds ) ");
            }else if (txnStaCds.length>0 && !txnStaCdsEqualFlag){
                StringBuffer txnStaCdStr = new StringBuffer();
                for(int i = 0 ;i< txnStaCds.length ; i++){
                    txnStaCdStr.append(txnStaCds[i] + ",") ;
                }
                sql.append("and c.TXN_STA_CD not in (" + txnStaCdStr.toString().substring(0,txnStaCdStr.toString().length()-1) + ") ");
//                sql.append("and c.TXN_STA_CD not in ( :txnStaCds ) ");
            }
            if(StringUtils.isNotBlank(createdTs)){
                sql.append("and c.CREATED_TS >= to_date( :createdTs , 'yyyyMMdd') ");
            }
            
            if(bankCardNameFalg){
                sql.append("and pay.recv_bank_acct_name = :bankCardName ");
            }else{
                sql.append("and pay.recv_bank_acct_name != :bankCardName ");
            }
            param.put("custId", custId);
            param.put("gopayIntTxnCd", gopayIntTxnCd);
//            param.put("txnStaCds", txnStaCds);
            param.put("createdTs", createdTs);
            param.put("bankCardName", SensitiveInfoUtils.smartEncryptWithCheck(bankCardName));
            logger.info("------sql:" + sql.toString() + ",param=" + param.toString() );

            long start = System.currentTimeMillis();
            List expendInfoList = this.findBySql(sql.toString(),param);
            logger.info("查询订单数量耗时:{}ms", (System.currentTimeMillis() - start));

            Object[] result = (Object[])expendInfoList.get(0);
            Integer count = new Integer(StringTools.checkNulltoString(result[0]));
            BigDecimal totalAmt = null;
            if(count.intValue() > 0){
                totalAmt = new BigDecimal(StringTools.checkNulltoString(result[1]));
            }else{
                totalAmt = new BigDecimal("0.00");
            }
            Map resultMap = new HashMap();
            resultMap.put("count", count);
            resultMap.put("totalAmt", totalAmt);
            return resultMap;
            
        }
        

        @Override
        public List<Object> findTimeoutOrdersInCBPay(Map param, int pageNo, int pageSize) {
       
                StringBuffer sql = new StringBuffer(getBaseSql()
                        + " and GOPAY_INT_TXN_CD in('01720','01723','01724','01740')")
                        .append(" and (((PAY_CHANNEL in ('01','02','03','04','06','08','70') or PAY_CHANNEL is null)");
                String b2bTimeSpan = String.valueOf(param.get("b2b"));
                String b2cTimeSpan = String.valueOf(param.get("b2c"));
                sql.append(getLifeCycleSql(b2cTimeSpan) + "b2cTimeSpan)");
                sql.append(" or (PAY_CHANNEL in ('51','52') "
                        + getLifeCycleSql(b2bTimeSpan) + "b2bTimeSpan))");
                Map m = new HashMap();
                m.put("b2cTimeSpan",
                        String.valueOf(param.get("b2c")).replaceAll("[a-zA-Z]+", ""));
                m.put("b2bTimeSpan",
                        String.valueOf(param.get("b2b")).replaceAll("[a-zA-Z]+", ""));
                List<?> result = findBySql(sql.toString(), m, pageNo, pageSize);
                return (List<Object>) result;
            
        }

        /**
         * 根据交易类型和状态查询制定日期订单集
         * @param txnStaCd
         * @param gopayIntTxnCd
         * @return
         */
        @Override
        public List<CpsGenMainOrder> findOrderByIntTxnCdAndTxnStaCd(int txnStaCd, String gopayIntTxnCd, Date tranFinishTime) {

//          String hql = "select * from CPS_GEN_MAIN_ORDER where txn_Sta_Cd = :txnStaCd "
//          + "and gopay_Int_Txn_Cd = :gopayIntTxnCd "
//          + "and tran_Finish_Time >= to_timestamp('"+DateUtils.formatDate(tranFinishTime,DateUtils.yyyy_MM_dd)+" 00:00:00.000000' , 'yyyy-MM-dd HH24:MI:ss.ff')  "
//          + "and tran_Finish_Time <= to_timestamp('"+DateUtils.formatDate(tranFinishTime,DateUtils.yyyy_MM_dd)+" 23:59:59.999999' , 'yyyy-MM-dd HH24:MI:ss.ff') ";

            String hql = " from CpsGenMainOrder where txnStaCd = :txnStaCd "
                    + "and gopayIntTxnCd = :gopayIntTxnCd "
                    + "and tranFinishTime >= :tranFinishTimeStartDt "
                    + "and tranFinishTime <= :tranFinishTimeEndDt ";
            
            Map<String, Object> param = new HashMap<String, Object>();
            param.put("txnStaCd", txnStaCd);
            param.put("gopayIntTxnCd", gopayIntTxnCd);
            param.put("tranFinishTimeStartDt", DateUtils.strToDate(DateUtils.formatDate(tranFinishTime,DateUtils.FILEDATE_PATTERM) +"000000"));
            param.put("tranFinishTimeEndDt", DateUtils.strToDate(DateUtils.formatDate(tranFinishTime,DateUtils.FILEDATE_PATTERM) +"235959"));
            logger.info("根据交易类型和状态查询制定日期订单集:" + hql +"," + param.toString());
            return findByHql(hql, param); 
        }

	@Override
	public List<CpsGenMainOrder> findOrdersByRelevanceGopayOrderIdAndGopayIntTxnCd(String relevanceGopayOrderId, String gopayIntTxnCd) {
		String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId and order.gopayIntTxnCd = :gopayIntTxnCd";
		Map params = new HashMap();
		params.put("RelevanceGopayOutOrderId", relevanceGopayOrderId);
		params.put("gopayIntTxnCd", gopayIntTxnCd);

		List<CpsGenMainOrder> res = super.findByHql(hql, params);
		if (res != null && res.size() > 0) {
			return res;
		}
		return null;
	}

	@Override
	public List<CpsGenMainOrder> findListByRelevanceGopayOrderIdAndGopayIntTxnCd(String relevanceGopayOrderId, String gopayIntTxnCd) {
		String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId and order.gopayIntTxnCd = :gopayIntTxnCd";
		Map params = new HashMap();
		params.put("RelevanceGopayOutOrderId", relevanceGopayOrderId);
		params.put("gopayIntTxnCd", gopayIntTxnCd);

		List<CpsGenMainOrder> res = super.findByHql(hql, params);
		if (res != null ) {
			return res;
		}
		return null;
	}

	@Override
	public boolean findSpiltOrder(String relevanceGopayOrderId,String mainRecvAcct){
		DetachedCriteria detachedCriteria = DetachedCriteria.forClass(CpsGenMainOrder.class);
		if(StringUtils.isNotBlank(relevanceGopayOrderId)){
			detachedCriteria.add(Restrictions.eq("relevanceGopayOrderId", relevanceGopayOrderId));
		}

		if(StringUtils.isNotBlank(mainRecvAcct)){
			detachedCriteria.add(Restrictions.eq("mainRecvAcct", mainRecvAcct));
		}
		detachedCriteria.add(Restrictions.eq("txnStaCd", TxnStaCode.TXN_STA_CD_30000.value));
		detachedCriteria.add(Restrictions.eq("gopayIntTxnCd", IntTxnCd._00606.value));
		List<CpsGenMainOrder> dicGopayApList = findByCriteria(detachedCriteria);
		if(dicGopayApList!=null && dicGopayApList.size()>0){
			return true;
		}
		return false;
	}

	@Override
	public List<CpsGenMainOrder> findListByRelevanceGopayOrderIdAndGopayIntTxnCdRecvAcct(String relevanceGopayOrderId, String gopayIntTxnCd, String recvAcct) {
		String hql = " from CpsGenMainOrder order where order.relevanceGopayOrderId = :RelevanceGopayOutOrderId and order.gopayIntTxnCd = :gopayIntTxnCd and order.mainRecvAcct = :mainRecvAcct order by order.gopayTxnTm desc";
		Map params = new HashMap();
		params.put("RelevanceGopayOutOrderId", relevanceGopayOrderId);
		params.put("gopayIntTxnCd", gopayIntTxnCd);
		params.put("mainRecvAcct",recvAcct);

		List<CpsGenMainOrder> res = super.findByHql(hql, params);
		if (res != null ) {
			return res;
		}
		return null;
	}
}